/*
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2, or (at your option)
 * any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
 * 02111-1307, USA.
 *
 * http://www.gnu.org/copyleft/gpl.html
 */
package l1j.server.server.datatables.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import l1j.opqlo.NewSystem.ItemLevelUp.ItemLevelUpTable;
import l1j.opqlo.Server.L1World.L1World;
import l1j.server.DatabaseFactory;
import l1j.server.server.datatables.CharObjidTable;
import l1j.server.server.datatables.ItemTable;
import l1j.server.server.datatables.storage.CharItemsStorage;
import l1j.server.server.model.Instance.L1ItemInstance;
import l1j.server.server.model.item.L1ItemId;
import l1j.server.server.templates.L1Item;
import l1j.server.server.utils.PerformanceTimer;
import l1j.server.server.utils.SQLUtil;

public class CharItemsTable implements CharItemsStorage {

	private static final Logger _log = Logger.getLogger(CharItemsTable.class.getName());

	// 背包物件清單 (人物OBJID) (物品清單)
	private static final Map<Integer, CopyOnWriteArrayList<L1ItemInstance>> _itemList =
			new ConcurrentHashMap<Integer, CopyOnWriteArrayList<L1ItemInstance>>();
 
	/**
	 * 建立資料
	 * @param objid
	 * @param item
	 */
	private static void addItem(final Integer objid, final L1ItemInstance item) {
		CopyOnWriteArrayList<L1ItemInstance> list = _itemList.get(objid);
		if (list == null) {// 該人物尚無背包數據
			list = new CopyOnWriteArrayList<L1ItemInstance>();
		}
		//		boolean delete = false;
		//		if(item.getItem().isStackable()){
		//			final int itemid = item.getItemId();
		//			for(L1ItemInstance srcitem : list){
		//				if(srcitem.getItemId() == itemid){
		//					final long count = srcitem.getCount() + item.getCount();//將身上的總數做相加
		//					srcitem.setCount(count);//重新設定數量
		//					delete = true;
		//				}
		//			}
		//		}
		if (!list.contains(item)) {// 清單中不包含該物品
			list.add(item);
		}
		// 世界物件中不包含該OBJID數據
		if (L1World.getInstance().findObject(item.getId()) == null) {
			L1World.getInstance().storeObject(item);
		}
		_itemList.put(objid, list);
		//		if(delete){
		//			deleteRepeatItem(objid, item); // 刪除重複的可推疊道具
		//		}
	}

	/**
	 * 刪除遺失資料
	 * @param objid
	 */
	private static void deleteItem(final Integer objid) {
		final CopyOnWriteArrayList<L1ItemInstance> list = _itemList.remove(objid);
		if (list != null) {
			// 移出世界
			for (final L1ItemInstance item : list) {
				L1World.getInstance().removeObject(item);
			}
		}

		Connection cn = null;
		PreparedStatement ps = null;
		try {
			cn = DatabaseFactory.get().getConnection();
			ps = cn.prepareStatement(
					"DELETE FROM `character_items` WHERE `char_id`=?");
			ps.setInt(1, objid);
			ps.execute();

		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);

		} finally {
			SQLUtil.close(ps);
			SQLUtil.close(cn);
		}
	}


	/**
	 * 刪除錯誤物品資料
	 * @param objid
	 */
	private static void errorItem(int objid) {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement(
					"DELETE FROM `character_items` WHERE `id`=?");
			pstm.setInt(1, objid);
			pstm.execute();

		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);

		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}

	/**
	 * 刪除指定編號全部數據
	 * @param itemid
	 */
	@Override
	public void del_item(final int itemid) {
		try {
			for (final Integer key : _itemList.keySet()) {
				// 人物背包
				final CopyOnWriteArrayList<L1ItemInstance> value = _itemList.get(key);
				for (final L1ItemInstance item : value) {
					if (item.getItemId() == itemid) {
						this.deleteItem(key, item);
					}
				}
			}

		} catch (final Exception e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		}
	}



	/**
	 * 刪除背包物品
	 * @param objid 人物OBJID
	 * @param item 物品
	 *
	 * @throws Exception
	 */
	@Override
	public void deleteItem(final int objid, final L1ItemInstance item) throws Exception {
		final CopyOnWriteArrayList<L1ItemInstance> list = _itemList.get(objid);
		if (list != null) {
			list.remove(item);

			Connection con = null;
			PreparedStatement pstm = null;
			try {
				con = DatabaseFactory.get().getConnection();
				pstm = con.prepareStatement("DELETE FROM character_items WHERE id = ?");
				pstm.setInt(1, item.getId());
				pstm.execute();
			} catch (final SQLException e) {
				_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);

			} finally {
				SQLUtil.close(pstm);
				SQLUtil.close(con);
			}
		}
	}
	/**
	 * 刪除人物背包資料(完整)
	 * @param objid
	 */
	@Override
	public void delUserItems(final Integer objid) {
		deleteItem(objid);
	}
	private void executeUpdate(int objId, String sql, double l)
			throws SQLException {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement(sql.toString());
			pstm.setDouble(1, l);
			pstm.setInt(2, objId);
			pstm.execute();
		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	private void executeUpdate(int objId, String sql, long l)
			throws SQLException {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement(sql.toString());
			pstm.setLong(1, l);
			pstm.setInt(2, objId);
			pstm.execute();
		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	private void executeUpdate(int objId, String sql, int i , String s)
			throws SQLException {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement(sql.toString());
			pstm.setInt(1, i);
			pstm.setString(2, s);
			pstm.setInt(3, objId);
			pstm.execute();
		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	private void executeUpdate(int objId, String sql, String l)
			throws SQLException {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement(sql.toString());
			pstm.setString(1, l);
			pstm.setInt(2, objId);
			pstm.execute();
		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	private void executeUpdate(int objId, String sql, Timestamp ts)
			throws SQLException {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement(sql.toString());
			pstm.setTimestamp(1, ts);
			pstm.setInt(2, objId);
			pstm.execute();
		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	/**
	 * 給予金幣(對離線人物)
	 * @param objid 人物OBJID
	 * @param count 金幣給予數量
	 * @throws Exception
	 */
	@Override
	public void getAdenaCount(final int objid, final long count) throws Exception {
		final CopyOnWriteArrayList<L1ItemInstance> list = _itemList.get(objid);
		if (list != null) {
			boolean isAdena = false;// 背包有金幣
			for (final L1ItemInstance item : list) {
				if (item.getItemId() == L1ItemId.ADENA) {
					// 更新數量
					item.setCount(item.getCount() + count);
					this.updateItemCount(item);
					isAdena = true;
				}
			}
			// 背包無金幣
			if (!isAdena) {
				final L1ItemInstance item = ItemTable.getInstance().createItem(L1ItemId.ADENA);
				item.setCount(count);
				this.storeItem(objid, item);
			}
		}
	}
	/**
	 * 傳回對應所有物品數量
	 * @param objId
	 * @return
	 * @throws Exception
	 */
	@Override
	public int getItemCount(int objId) throws Exception {
		int count = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con
					.prepareStatement("SELECT * FROM character_items WHERE char_id = ?");
			pstm.setInt(1, objId);
			rs = pstm.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return count;
	}
	/**
	 * 是否有指定數據
	 * @param pcObjid
	 * @param objid
	 * @param count
	 * @return
	 */
	@Override
	public boolean getUserItem(final int objid) {
		for (final CopyOnWriteArrayList<L1ItemInstance> list : _itemList.values()) {
			for (final L1ItemInstance item : list) {
				if (item.getId() == objid) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 傳回擁有該物品ID的人物清單<BR>
	 * (適用該物品每人只能傭有一個的狀態)
	 * @param itemid
	 * @return
	 */
	@Override
	public Map<Integer, L1ItemInstance> getUserItems(final int itemid) {
		// 人物OBJID / 物品
		final Map<Integer, L1ItemInstance> outList = new ConcurrentHashMap<Integer, L1ItemInstance>();
		try {
			for (final Integer key : _itemList.keySet()) {
				final CopyOnWriteArrayList<L1ItemInstance> value = _itemList.get(key);
				for (final L1ItemInstance item : value) {
					if (item.getItemId() == itemid) {
						outList.put(key, item);
					}
				}
			}

		} catch (final Exception e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);

		}
		return outList;
	}

	/**
	 * 該人物背包是否有指定數據
	 * @param pcObjid
	 * @param objid
	 * @param count
	 * @return
	 */
	@Override
	public boolean getUserItems(final Integer pcObjid, final int objid, final long count) {
		final CopyOnWriteArrayList<L1ItemInstance> list = _itemList.get(pcObjid);
		if (list != null) {
			for (final L1ItemInstance item : list) {
				if (item.getId() == objid && item.getCount() >= count) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 資料預先載入
	 */
	@Override
	public void load() {
		final PerformanceTimer timer = new PerformanceTimer();
		int i = 0;

		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement( "SELECT * FROM `character_items`");
			rs = pstm.executeQuery();

			while (rs.next()) {
				L1ItemInstance item;
				final int objid = rs.getInt("id");
				final int item_id = rs.getInt("item_id");
				final int char_id = rs.getInt("char_id");

				// 檢查該資料所屬是否遺失
				if (CharObjidTable.get().isChar(char_id) != null) {
					final L1Item itemTemplate = ItemTable.getInstance().getTemplate(item_id);
					if (itemTemplate == null) {
						// 無該物品資料 移除
						errorItem(objid);
						continue;
					}

					item = new L1ItemInstance();
					item.setId(rs.getInt("id"));
					item.setItem(itemTemplate);
					item.setRemainingTime(rs.getInt("remaining_time")); // opqlo 測試防止計時物品 讀取時消失問題
					item.setCount(rs.getInt("count"));
					item.setEquipped(rs.getInt("Is_equipped") != 0 ? true : false);
					item.setEnchantLevel(rs.getInt("enchantlvl"));
					item.setIdentified(rs.getInt("is_id") != 0 ? true : false);
					item.set_durability(rs.getInt("durability"));
					item.setChargeCount(rs.getInt("charge_count"));
					item.setLastUsed(rs.getTimestamp("last_used"));
					item.setBless(rs.getInt("bless"));
					item.setAttrEnchantKind(rs.getInt("attr_enchant_kind"));
					item.setAttrEnchantLevel(rs.getInt("attr_enchant_level"));
					//TODO 飾品強化卷軸
					item.setFireMr(rs.getInt("firemr"));
					item.setWaterMr(rs.getInt("watermr"));
					item.setEarthMr(rs.getInt("earthmr"));
					item.setWindMr(rs.getInt("windmr"));
					item.setaddSp(rs.getInt("addsp"));
					item.setaddHp(rs.getInt("addhp"));
					item.setaddMp(rs.getInt("addmp"));
					item.setHpr(rs.getInt("hpr"));
					item.setMpr(rs.getInt("mpr"));
					//TODO 飾品強化卷軸
					//TODO 魔法武器卷軸by missu0524
					item.setSkillType(rs.getInt("skilltype"));
					//TODO 魔法武器卷軸by missu0524
					// opqlo道具天數刪除系統
					item.setDeleteDate(rs.getTimestamp("DeleteDate"));
					//opqlo 道具升級系統START
					final int level = rs.getInt("level");
					if(level > 1){
						item.setLevel(level);
						ItemLevelUpTable.getInstance().getLevelStatus(item);
					}
					//opqlo 道具升級系統END
					item.getLvStatus().setItemSpecialDmg(item , rs.getString("屬性增傷") ,0);
					item.getLvStatus().setItemSpecialDmg(item , rs.getString("六屬性加成") ,1);
					item.getLvStatus().setItemSpecialDmg(item, rs.getString("種族增傷"), 2);
					item.setExtraDmg(rs.getInt("破甲"));
					item.setExtraDef(rs.getInt("抵擋"));
					item.setModDmg(rs.getInt("額外攻擊"));
					item.setModHit(rs.getInt("攻擊成功"));
					item.setExpUp(rs.getDouble("經驗"));
					item.setDoubleDmg(rs.getInt("爆擊"));
					item.setBigDmg(rs.getInt("大怪"));
					item.setSmallDmg(rs.getInt("小怪"));
					item.setaddMr(rs.getInt("魔防"));
					item.setStunDice(rs.getInt("暈眩"));
					item.setStoneDice(rs.getInt("石化"));
					item.setSleepDice(rs.getInt("睡眠"));
					item.setFreezeDice(rs.getInt("凍結"));
					item.setSustainDice(rs.getInt("支撐"));
					item.setBlindDice(rs.getInt("暗黑"));
					item.setStunMr(rs.getInt("暈眩抗性"));
					item.setStoneMr(rs.getInt("石化抗性"));
					item.setSleepMr(rs.getInt("睡眠抗性"));
					item.setFreezeMr(rs.getInt("凍結抗性"));
					item.setSustainMr(rs.getInt("支撐抗性"));
					item.setBlindMr(rs.getInt("暗黑抗性"));
					item.addDroprate(rs.getDouble("掉寶率")); //opqlo 掉寶率
					item.getLvStatus().setAttrDef(1, rs.getDouble("地減傷"));
					item.getLvStatus().setAttrDef(2, rs.getDouble("火減傷"));
					item.getLvStatus().setAttrDef(4, rs.getDouble("水減傷"));
					item.getLvStatus().setAttrDef(5, rs.getDouble("光減傷"));
					item.getLvStatus().setAttrDef(6, rs.getDouble("闇減傷"));
					item.getLvStatus().setAttrDef(8, rs.getDouble("風減傷"));
					item.setMagicDmgMod(rs.getInt("魔法增傷"));
					if(item.getDeleteDate() !=null){
						final Calendar cal = Calendar.getInstance();
						final long checkDeleteDate = (cal.getTimeInMillis() - item.getDeleteDate().getTime()) / 1000;
						if (checkDeleteDate >= 0) {
							// 時間超時 移除
							errorItem(objid);
							continue;
						}
					}
					item.getLastStatus().updateAll();
					addItem(char_id, item);
					i++;
				} else {
					deleteItem(char_id);
				}
			}

		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(rs ,pstm , con);
		}
		_log.info("載入人物背包物件清單資料數量: " + _itemList.size() + "/" + i + "(" + timer.get()+ "ms)");
	}


	/**
	 * 傳回該人物背包資料
	 * @param objid
	 * @return
	 */
	@Override
	public CopyOnWriteArrayList<L1ItemInstance> loadItems(final Integer objid) {
		final CopyOnWriteArrayList<L1ItemInstance> list = _itemList.get(objid);
		if (list != null) {
			return list;
		}
		return null;
	}

	/**
	 * 增加背包物品
	 * @param objId
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void storeItem(final int objId, final L1ItemInstance item) throws Exception {
		addItem(objId, item);
		item.getLastStatus().updateAll();
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement("INSERT INTO character_items SET id = ?, item_id = ?, char_id = ?, item_name = ?, count = ?, is_equipped = 0, enchantlvl = ?, is_id = ?, durability = ?, charge_count = ?, remaining_time = ?, last_used = ?, bless = ?, attr_enchant_kind = ?, attr_enchant_level = ?, firemr = ?, watermr = ?, earthmr = ?, windmr = ?, addsp = ?, addhp = ?, addmp = ?, hpr = ?, mpr = ?, SkillType= ?, DeleteDate = ?, level=?, 屬性增傷=?, 六屬性加成=?, 破甲=?, 抵擋=?, 額外攻擊=?, 攻擊成功=?, 經驗=?, 爆擊=?, 大怪=?, 小怪=?, 魔防=?,種族增傷=? ,暈眩 =?, 石化 =?, 睡眠 =?, 凍結 =?, 支撐 =?, 暗黑 =? , 暈眩抗性=?, 石化抗性=? , 睡眠抗性=? , 凍結抗性=? , 支撐抗性=? , 暗黑抗性=?, 掉寶率=? ,地減傷=?,火減傷=?,水減傷=?,光減傷=?,闇減傷=?,風減傷=?,魔法增傷=?");
			pstm.setInt(1, item.getId());
			pstm.setInt(2, item.getItem().getItemId());
			pstm.setInt(3, objId);
			pstm.setString(4, item.getItem().getName());
			pstm.setLong(5, item.getCount());
			pstm.setInt(6, item.getEnchantLevel());
			pstm.setInt(7, item.isIdentified() ? 1 : 0);
			pstm.setInt(8, item.get_durability());
			pstm.setInt(9, item.getChargeCount());
			pstm.setInt(10, item.getRemainingTime());
			pstm.setTimestamp(11, item.getLastUsed());
			pstm.setInt(12, item.getBless());
			pstm.setInt(13, item.getAttrEnchantKind());
			pstm.setInt(14, item.getAttrEnchantLevel());
			//TODO 飾品強化卷軸修正無法存取人物物品資料
			pstm.setInt(15, item.getFireMr());
			pstm.setInt(16, item.getWaterMr());
			pstm.setInt(17, item.getEarthMr());
			pstm.setInt(18, item.getWindMr());
			pstm.setInt(19, item.getaddSp());
			pstm.setInt(20, item.getaddHp());
			pstm.setInt(21, item.getaddMp());
			pstm.setInt(22, item.getHpr());
			pstm.setInt(23, item.getMpr());
			//TODO 飾品強化卷軸修正無法存取人物物品資料
			//TODO 魔法武器卷軸by missu0524
			pstm.setInt(24, item.getSkillType());
			//TODO 魔法武器卷軸by missu0524
			pstm.setTimestamp(25, item.getDeleteDate()); //opqlo 道具天數刪除系統
			//opqlo 道具升級系統START
			pstm.setInt(26, item.getLevel());
			//opqlo 道具升級系統END

			pstm.setString(27, item.getLvStatus().saveItemSpecialDmg(0));
			pstm.setString(28, item.getLvStatus().saveItemSpecialDmg(1));
			pstm.setInt(29, item.getExtraDmg());
			pstm.setInt(30, item.getExtraDef());
			pstm.setInt(31, item.getModDmg());
			pstm.setInt(32, item.getModHit());
			pstm.setDouble(33, item.getExpUp());
			pstm.setDouble(34, item.getDoubleDmg());
			pstm.setDouble(35, item.getBigDmg());
			pstm.setDouble(36, item.getSmallDmg());
			pstm.setInt(37, item.getaddMr());
			pstm.setString(38, item.getLvStatus().saveItemSpecialDmg(2));
			pstm.setInt(39,item.getStunDice());
			pstm.setInt(40,item.getStoneDice());
			pstm.setInt(41,item.getSleepDice());
			pstm.setInt(42,item.getFreezeDice());
			pstm.setInt(43,item.getSustainDice());
			pstm.setInt(44,item.getBlindDice());
			pstm.setInt(45, item.getStunMr());
			pstm.setInt(46, item.getStoneMr());
			pstm.setInt(47, item.getSleepMr());
			pstm.setInt(48, item.getFreezeMr());
			pstm.setInt(49, item.getSustainMr());
			pstm.setInt(50, item.getBlindMr());
			pstm.setDouble(51, item.getDroprate());
			/**六屬性減傷**/
			final HashMap<Integer , Double>attrdef = item.getLvStatus().getAttrDef();
			final int[]keylist = {1,2,4,5,6,8};
			for(final int key : keylist){
				final Double value;
				if(attrdef.containsKey(key)){
					value = attrdef.get(key);
				}else{
					value =(double) 0;
				}

				switch(key){
				case 1:
					pstm.setDouble(52, value);
					break;
				case 2:
					pstm.setDouble(53, value);
					break;
				case 4:
					pstm.setDouble(54, value);
					break;
				case 5:
					pstm.setDouble(55, value);
					break;
				case 6:
					pstm.setDouble(56, value);
					break;
				case 8:
					pstm.setDouble(57, value);
					break;
				}
		}
			pstm.setInt(58, item.getMagicDmgMod());
			pstm.execute();

		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,"背包物品增加時發生異常 人物OBJID:" + objId, e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}

	@Override
	public void updaOther(L1ItemInstance item) throws Exception {

		Connection con = null;
		PreparedStatement pstm = null;
		try {
			int i=1;
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement("UPDATE character_items SET 破甲=?,抵擋=?,額外攻擊=?,攻擊成功=?,爆擊=?,大怪=?,小怪=?,魔防=? ,暈眩 =?, 石化 =?, 睡眠 =?, 凍結 =?, 支撐 =?, 暗黑 =?  WHERE id = ?");
			pstm.setInt(i++, item.getExtraDmg());//破甲
			pstm.setInt(i++, item.getExtraDef());//抵擋
			pstm.setInt(i++, item.getModDmg());//額外攻擊
			pstm.setInt(i++, item.getModHit());//攻擊成功
			pstm.setInt(i++, item.getDoubleDmg());//爆擊
			pstm.setDouble(i++, item.getBigDmg());//大怪
			pstm.setDouble(i++, item.getSmallDmg());//小怪
			pstm.setInt(i++, item.getaddMr());//魔防
			pstm.setInt(i++,item.getStunDice());
			pstm.setInt(i++,item.getStoneDice());
			pstm.setInt(i++,item.getSleepDice());
			pstm.setInt(i++,item.getFreezeDice());
			pstm.setInt(i++,item.getSustainDice());
			pstm.setInt(i++,item.getBlindDice());
			pstm.setInt(i++, item.getId());
			pstm.execute();

		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}

	@Override
	public void updateaddHp(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET addhp = ? WHERE id = ?", item.getaddHp());
		item.getLastStatus().updateaddHp();
	}

	@Override
	public void updateaddMp(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET addmp = ? WHERE id = ?", item.getaddMp());
		item.getLastStatus().updateaddMp();
	}

	/**
	 * 刪除遺失資料
	 * @param objid
	 */
	//	private static void deleteRepeatItem(final Integer objid , final L1ItemInstance item) {
	//		CopyOnWriteArrayList<L1ItemInstance> list = _itemList.get(objid);
	//		if (list != null) {
	//			// 移出世界
	//			if(list.remove(item)){
	//				L1World.getInstance().removeObject(item);
	//			}
	//		}
	//
	//		Connection cn = null;
	//		PreparedStatement ps = null;
	//		try {
	//			cn = DatabaseFactory.get().getConnection();
	//			ps = cn.prepareStatement("DELETE FROM `character_items` WHERE `char_id`=? && `id`=?");
	//			ps.setInt(1, objid);
	//			ps.setInt(2, item.getId());
	//			ps.execute();
	//
	//		} catch (final SQLException e) {
	//			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);
	//		} finally {
	//			SQLUtil.close(ps);
	//			SQLUtil.close(cn);
	//		}
	//	}


	@Override
	public void updateaddMr(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 魔防 = ? WHERE id = ?", item.getaddMr());
		item.getLastStatus().updateaddMr();
	}
	@Override
	public void updateaddSp(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET addsp = ? WHERE id = ?", item.getaddSp());
		item.getLastStatus().updateSp();
	}

	@Override
	public void updateBigDmg(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 大怪 = ? WHERE id = ?", item.getBigDmg());
		item.getLastStatus().updateBigDmg();
	}

	@Override
	public void updateBlindDice(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 暗黑 = ? WHERE id = ?", item.getBlindDice());
		item.getLastStatus().updateBlindDice();
	}

	//opqlo 道具天數刪除系統
	@Override
	public void updateDeleteDate(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),"UPDATE character_items SET DeleteDate = ? WHERE id = ?",item.getDeleteDate());
		item.getLastStatus().updateDeleteDate();
	}


	@Override
	public void updateDoubleDmg(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 爆擊 = ? WHERE id = ?", item.getDoubleDmg());
		item.getLastStatus().updateDoubleDmg();
	}

	@Override
	public void updateEarthMr(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET earthmr = ? WHERE id = ?", item.getEarthMr());
		item.getLastStatus().updateEarthMr();
	}

	@Override
	public void updateExp(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 經驗 = ? WHERE id = ?", item.getExpUp());
		item.getLastStatus().updateSkillType();
	}

	@Override
	public void updateExtraDef(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 抵擋 = ? WHERE id = ?", item.getExtraDef());
		item.getLastStatus().updateExtraDef();
	}

	@Override
	public void updateExtraDmg(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 破甲 = ? WHERE id = ?", item.getExtraDmg());
		item.getLastStatus().updateExtraDmg();
	}

	//TODO 飾品強化卷軸
	@Override
	public void updateFireMr(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET firemr = ? WHERE id = ?", item.getFireMr());
		item.getLastStatus().updateFireMr();
	}
	@Override
	public void updateFreezeDice(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 凍結 = ? WHERE id = ?", item.getFreezeDice());
		item.getLastStatus().updateFreezeDice();
	}
	@Override
	public void updateHpr(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET hpr = ? WHERE id = ?", item.getHpr());
		item.getLastStatus().updateHpr();
	}

	//opqlo 道具升級系統END

	/**
	 * 更新強化屬性
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemAttrEnchantKind(L1ItemInstance item) throws Exception {
		this.executeUpdate(
				item.getId(),
				"UPDATE character_items SET attr_enchant_kind = ? WHERE id = ?",item.getAttrEnchantKind());
		item.getLastStatus().updateAttrEnchantKind();
	}
	/**
	 * 更新強化屬性強化度
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemAttrEnchantLevel(L1ItemInstance item)throws Exception {
		this.executeUpdate(
				item.getId(),
				"UPDATE character_items SET attr_enchant_level = ? WHERE id = ?",item.getAttrEnchantLevel());
		item.getLastStatus().updateAttrEnchantLevel();
	}
	/**
	 * 更新祝福狀態
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemBless(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET bless = ? WHERE id = ?",item.getBless());
		item.getLastStatus().updateBless();
	}
	/**
	 * 更新可用次數
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemChargeCount(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),"UPDATE character_items SET charge_count = ? WHERE id = ?",item.getChargeCount());
		item.getLastStatus().updateChargeCount();
	}
	/**
	 * 更新數量
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemCount(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),"UPDATE character_items SET count = ? WHERE id = ?",Math.min(2000000000 , item.getCount()));
		item.getLastStatus().updateCount();
	}
	/**
	 * 更新最後使用時間
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemDelayEffect(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET last_used = ? WHERE id = ?",item.getLastUsed());
		item.getLastStatus().updateLastUsed();
	}
	/**
	 * 更新損壞度
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemDurability(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET durability = ? WHERE id = ?",item.get_durability());
		item.getLastStatus().updateDuraility();
	}
	/**
	 * 更新強化度
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemEnchantLevel(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET enchantlvl = ? WHERE id = ?",item.getEnchantLevel());
		item.getLastStatus().updateEnchantLevel();
	}
	/**
	 * 更新使用狀態
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemEquipped(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET is_equipped = ? WHERE id = ?",item.isEquipped() ? 1 : 0);
		item.getLastStatus().updateEquipped();
	}

	/**
	 * 更新ITEMID
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemId(final L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE `character_items` SET `item_id`=?, `item_name`=? WHERE `id`=?",
				item.getItemId(),
				item.getItem().getName()
				);
		item.getLastStatus().updateItemId();
	}
	/**
	 * 更新物品ITEMID 與中文名稱
	 * @param item
	 */
	@Override
	public void updateItemId_Name(final L1ItemInstance item) throws Exception {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement("UPDATE `character_items` SET `item_id`=?,`item_name`=?,`bless`=? WHERE `id`=?");
			pstm.setInt(1, item.getItemId());
			pstm.setString(2, item.getItem().getName());
			pstm.setInt(3, item.getItem().getBless());
			pstm.setInt(4, item.getId());
			pstm.execute();

		} catch (final SQLException e) {
			_log.log(Level.SEVERE ,e.getLocalizedMessage(), e);

		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	/**
	 * 更新鑑定狀態
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemIdentified(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET is_id = ? WHERE id = ?",item.isIdentified() ? 1 : 0);
		item.getLastStatus().updateIdentified();
	}
	/**
	 * 更新可用時間
	 * @param item
	 * @throws Exception
	 */
	@Override
	public void updateItemRemainingTime(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET remaining_time = ? WHERE id = ?",item.getRemainingTime());
		item.getLastStatus().updateRemainingTime();
	}
	//opqlo 道具升級系統START
	@Override
	public void updateLevel(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET level = ? WHERE id = ?", item.getLevel());
		item.getLastStatus().updateLevel();
	}
	@Override
	public void updateModDmg(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 額外攻擊 = ? WHERE id = ?", item.getModDmg());
		item.getLastStatus().updateModDmg();
	}
	@Override
	public void updateModHit(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 攻擊成功 = ? WHERE id = ?", item.getModHit());
		item.getLastStatus().updateModHit();
	}
	@Override
	public void updateMpr(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET mpr = ? WHERE id = ?", item.getMpr());
		item.getLastStatus().updateMpr();
	}
	//TODO 飾品強化卷軸
	//TODO 魔法武器卷軸by missu0524
	@Override
	public void updateSkillType(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET skilltype = ? WHERE id = ?", item.getSkillType());
		item.getLastStatus().updateSkillType();
	}
	//TODO 魔法武器卷軸by missu0524
	@Override
	public void updateSleepDice(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 睡眠 = ? WHERE id = ?", item.getSleepDice());
		item.getLastStatus().updateSleepDice();
	}
	@Override
	public void updateSmallDmg(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 小怪 = ? WHERE id = ?", item.getSmallDmg());
		item.getLastStatus().updateSmallDmg();
	}
	@Override
	public void updateStoneDice(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 石化 = ? WHERE id = ?", item.getStoneDice());
		item.getLastStatus().updateStoneDice();
	}
	@Override
	public void updateStunDice(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 暈眩 = ? WHERE id = ?", item.getStunDice());
		item.getLastStatus().updateStunDice();
	}
	@Override
	public void updateSustainDice(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 支撐 = ? WHERE id = ?", item.getSustainDice());
		item.getLastStatus().updateSustainDice();
	}
	@Override
	public void updateWaterMr(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET watermr = ? WHERE id = ?", item.getWaterMr());
		item.getLastStatus().updateWaterMr();
	}
	@Override
	public void updateWindMr(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET windmr = ? WHERE id = ?", item.getWindMr());
		item.getLastStatus().updateWindMr();
	}
	@Override
	public void update六屬性加成(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 六屬性加成 = ? WHERE id = ?", item.getLvStatus().saveItemSpecialDmg(1));
		item.getLastStatus().update六屬性加成();
	}
	@Override
	public void update種族增傷(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 種族增傷 = ? WHERE id = ?", item.getLvStatus().saveItemSpecialDmg(2));
		item.getLastStatus().update種族增傷();
	}
	@Override
	public void update屬性增傷(L1ItemInstance item) throws Exception {
		this.executeUpdate(item.getId(),
				"UPDATE character_items SET 屬性增傷 = ? WHERE id = ?", item.getLvStatus().saveItemSpecialDmg(0));
		item.getLastStatus().update屬性增傷();
	}
}
